package com.example.dao;

import com.example.entity.City;
import com.example.entity.Flight;
import com.example.entity.FlightDetail;
import com.example.entity.Ticket;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;

public interface FlightMapper {

    @Select("SELECT city FROM ftos.city ORDER BY a_point_num DESC LIMIT 4")
    List<String> getTop4ArrivalCities();
    @Select("SELECT * FROM ftos.city ORDER BY d_point_num DESC LIMIT 4")
    List<String> getTop4DepartureCities();
    @Select("SELECT * FROM ftos.flight WHERE departure_city = #{departure} AND arrival_city = #{destination} AND departure_date = #{departureDate} ORDER BY economy_price LIMIT 1")
    Flight searchCheapestFlight(@Param("departure") String departure, @Param("destination") String destination, @Param("departureDate") String departureDate);
    @Select("select near_city from ftos.city where city = #{city}")
    String isCityExist(String city);
    @Select("select * from ftos.city where city = #{city}")
    City getNearCity(String city);
    @Select("SELECT f.* " +
            "FROM ftos.flight f " +
            "JOIN ftos.flight_status fs ON f.f_id = fs.f_id " +
            "WHERE f.departure_city = #{departure} " +
            "  AND f.arrival_city = #{destination} " +
            "  AND f.departure_date = #{departureDate} " +
            "  AND fs.status NOT IN ('已起飞', '已取消', '已到达')")
    List<Flight> searchFlights(@Param("departure") String departure, @Param("destination") String destination, @Param("departureDate") Date departureDate);
    @Select("select * from ftos.flight f join ftos.plane p on f.plane_id = p.plane_id join ftos.flight_status fs on f.f_id = fs.f_id where departure_city = #{departure} AND arrival_city = #{destination} AND DATE(departure_date) = #{departureDate} AND fs.status NOT IN ('已起飞', '已取消', '已到达')")
    List<FlightDetail> searchFlightDetails(@Param("departure") String departure, @Param("destination") String destination, @Param("departureDate") Date departureDate);
    @Update("update ftos.city set a_point_num = a_point_num + 1 where city = #{city}")
    int addACityPoint(String city);
    @Update("update ftos.city set d_point_num = d_point_num + 1 where city = #{city}")
    int addDCityPoint(String city);
    //添加航班
    @Insert("insert into ftos.plane(p_model, p_airline, build_date, WiFi, capacity, economy_seat, business_seat, first_seat) values(#{p_model},#{p_airline},#{build_date},#{WiFi},#{capacity},#{economy_seat},#{business_seat},#{first_seat})")
    int addPlane(@Param("p_model") String p_model, @Param("p_airline") String p_airline, @Param("build_date") String build_date, @Param("WiFi") String WiFi, @Param("capacity") int capacity, @Param("economy_seat") int economy_seat, @Param("business_seat") int business_seat, @Param("first_seat") int first_seat);
    @Select("SELECT * FROM ftos.plane ORDER BY plane_id DESC LIMIT 1")
    int getLastPlaneId();
    @Insert("insert into ftos.flight(f_model,plane_id,f_airline,departure_airport,arrival_airport,departure_city,arrival_city,departure_date,departure_time,arrival_date,arrival_time,distance,economy_price,business_price,first_price,baggage_limit,meal,meal_money,gate) " +
            "values(#{f_model},#{plane_id},#{f_airline},#{departure_airport},#{arrival_airport},#{departure_city},#{arrival_city},#{departure_date},#{departure_time},#{arrival_date},#{arrival_time},#{distance},#{economy_price},#{business_price},#{first_price},#{baggage_limit},#{meal},#{meal_money},#{gate})")
    int addFlight(@Param("f_model") String f_model, @Param("plane_id") int plane_id, @Param("f_airline") String f_airline, @Param("departure_airport") String departure_airport, @Param("arrival_airport") String arrival_airport, @Param("departure_city") String departure_city, @Param("arrival_city") String arrival_city, @Param("departure_date") String departure_date, @Param("departure_time")String departure_time, @Param("arrival_date") String arrival_date, @Param("arrival_time") String arrival_time, @Param("distance") double distance, @Param("economy_price") double economy_price, @Param("business_price") double business_price, @Param("first_price") double first_price, @Param("baggage_limit") int baggage_limit, @Param("meal") String meal, @Param("meal_money") String meal_money, @Param("gate") String gate);
    @Select("SELECT * FROM ftos.flight ORDER BY f_id DESC LIMIT 1")
    int getLastFlightId();
    @Insert("insert into ftos.flight_status(f_id, date, captain, assistant_captain, chief_attendant, flight_crew_num, economy_space, first_space, business_space, status) " +
            "values (#{f_id},#{date},#{captain},#{assistant_captain},#{chief_attendant},#{flight_crew_num},#{economy_space},#{first_space},#{business_space},#{status})")
    int addFlightStatus(@Param("f_id") int f_id, @Param("date") String date, @Param("captain") String captain, @Param("assistant_captain") String assistant_captain, @Param("chief_attendant") String chief_attendant, @Param("flight_crew_num") int flight_crew_num, @Param("economy_space") int economy_space, @Param("first_space") int first_space, @Param("business_space") int business_space, @Param("status") String status);

    //改签
    @Select("select * from ftos.ticket where id = #{t_id}")
    Ticket getTicketById(int t_id);
    @Select("select * from ftos.flight where f_id = #{f_id}")
    Flight getFlightById(int f_id);
}
